package com.hyjx.framework.component.excel;

import com.hyjx.framework.common.CommonUtil;
import com.hyjx.framework.common.util.SpringBeanUtil;
import com.hyjx.framework.component.excel.column.ExcelTemplateColumn;
import com.hyjx.framework.component.excel.column.ExcelTemplateColumnService;
import com.hyjx.framework.component.excel.template.ExcelTemplate;
import com.hyjx.framework.orm.dao.IBaseDao;
import com.hyjx.framework.service.ConfigManager;
import com.hyjx.framework.service.SessionManager;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

/**
 * 导入公用组件（支持excel和csv导入）
 *
 * @author tiank
 * 2016-08-23
 */
public class ImportUtil {

    ExcelTemplateColumnService excelTemplateColumnService = SpringBeanUtil.getBean("excelTemplateColumnService", ExcelTemplateColumnService.class);
    HttpServletRequest req = null;
    @SuppressWarnings("rawtypes")
    Class modelClass = null;
    List<ExcelTemplateColumn> list = null;
    Map<String, String> paraMap = new HashMap<String, String>();
    public ExcelTemplate excelTemplate = null;
    IBaseDao dao = (IBaseDao) SpringBeanUtil.getBean("baseDao");
    @SuppressWarnings("rawtypes")
    List dataMaps = new ArrayList();
    @SuppressWarnings("rawtypes")
    List succMaps = new ArrayList();
    @SuppressWarnings("rawtypes")
    List errorMaps = new ArrayList();
    @SuppressWarnings("rawtypes")
    List successModels = new ArrayList();
    @SuppressWarnings("rawtypes")
    List errorsModels = new ArrayList();
    @SuppressWarnings("rawtypes")
    List errMsg = new ArrayList();
    //Map<String, Object> resultMap = new HashMap<>();
    int totalNumber = 0;

    public static ImportUtil getInstance(HttpServletRequest req, String en_module_name) {
        return new ImportUtil(req, en_module_name);
    }

    /**
     * @param req        request 对象
     * @param modelClass 配置实体对应的类的Class对象
     * @param handleType 1导入操作，2导出操作
     */
    @SuppressWarnings({})
    public ImportUtil(HttpServletRequest req, String en_module_name) {
        try {
            ExcelTemplateColumnService excelTemplateColumnService = SpringBeanUtil.getBean("excelTemplateColumnService", ExcelTemplateColumnService.class);
            ExcelTemplate et = excelTemplateColumnService.getExcelTemplate(en_module_name);
            this.req = req;
            this.excelTemplate = et;
            if (excelTemplate.getClass_name() != null) {
                this.modelClass = Class.forName(excelTemplate.getClass_name());
            }
            ExcelTemplateColumn dfc = new ExcelTemplateColumn();
            dfc.setPub_excel_template_id(excelTemplate.getPub_excel_template_id());
            list = excelTemplateColumnService.list(dfc);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 进行导入操作
     *
     * @param request
     * @return
     */
    @SuppressWarnings({"rawtypes"})
    public ExcelImportResult doImport() {
        Iterator<String> iterator = ((MultipartHttpServletRequest) this.req).getFileNames();
        Map returnMap = null;
        while (iterator.hasNext()) {
            String fileName = iterator.next();
            MultipartFile multipartFile = ((MultipartHttpServletRequest) this.req).getFile(fileName);
            try {
                String filename = multipartFile.getOriginalFilename();
                String path = ConfigManager.getAttatchmentsPath();
                File upload = new File(path);
                if (!upload.exists())
                    upload.mkdirs();
                File source = new File(path + filename);
                multipartFile.transferTo(source);
                readxls(source);
                return getReturnMap(this.req);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    /**
     * 读取excel中的数据
     *
     * @param file
     */
    @SuppressWarnings({"resource"})
    private void readxls(File file) {
        FileInputStream fi = null;
        POIFSFileSystem fs = null;
        try {
        /*	if(file.getName().toLowerCase().endsWith(".xls"))
			{
				fi = new FileInputStream(file);
				fs = new POIFSFileSystem(fi);
				@SuppressWarnings("unused")
				HSSFWorkbook wb =  new HSSFWorkbook(fs);
				
			}else*/
            {
                Workbook wb = null;
                try {
                    if (file.getName().toLowerCase().endsWith(".xls")) {
                        fi = new FileInputStream(file);
                        //fs = new POIFSFileSystem(fi);
                        wb = new HSSFWorkbook(fi);
                    } else if (file.getName().toLowerCase().endsWith(".xlsx")) {
                        wb = new XSSFWorkbook(file.getAbsolutePath());
                    }
                    Sheet sheet = (Sheet) wb.getSheetAt(0);
                    totalNumber = sheet.getPhysicalNumberOfRows();
                    for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++)//从第一行开始
                    {

                        Row row = sheet.getRow(i);
                        Object model = null;

                        if (this.modelClass != null) {
                            model = this.modelClass.newInstance();
                        }

                        /**
                         * 给model属性赋值
                         */
                        setModelAttribute(row, model);
                        /**
                         * 校验model
                         */
                        validate(i, model);
                    }

                } catch (Exception ex) {
                    ex.printStackTrace();
                } finally {
                    if (wb != null) {
                        wb.close();
                    }
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (fi != null)
                    fi.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 给model/map设置属性或者填充数据
     */
    @SuppressWarnings("unchecked")
    public void setModelAttribute(Row row, Object model) {
        try {
            if (this.modelClass != null) {
                Field[] fs = this.modelClass.getDeclaredFields();
                for (Field f : fs) {
                    for (ExcelTemplateColumn dfc : list) {
                        if (f.getName().equals(dfc.getEn_column_name())) {
                            f.setAccessible(true);
                            String type = f.getType().toString();//得到此属性的类型
                            if (row.getCell(Integer.parseInt(dfc.getExcel_field_no())) != null) {
                                row.getCell(Integer.parseInt(dfc.getExcel_field_no())).setCellType(Cell.CELL_TYPE_STRING);
                                if (type.endsWith("String")) {
                                    f.set(model, row.getCell(Integer.parseInt(dfc.getExcel_field_no())).getStringCellValue());        //给属性设值
                                } else if (type.endsWith("int") || type.endsWith("Integer")) {
                                    Integer tmpInt = Integer.parseInt(row.getCell(Integer.parseInt(dfc.getExcel_field_no())).getStringCellValue());
                                    f.set(model, tmpInt);       //给属性设值
                                }
                            }
                        }
                    }
                }
            } else {

                Map<String, Object> modelMap = new HashMap<String, Object>();
                for (ExcelTemplateColumn dfc : list) {
                    if (row.getCell(Integer.parseInt(dfc.getExcel_field_no())) != null) {
                        row.getCell(Integer.parseInt(dfc.getExcel_field_no())).setCellType(Cell.CELL_TYPE_STRING);
                        modelMap.put(dfc.getEn_column_name(), row.getCell(Integer.parseInt(dfc.getExcel_field_no())).getStringCellValue());
                    }


                }
                this.dataMaps.add(modelMap);
            }


        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    /**
     * 返回处理结果
     *
     * @param request
     * @return
     */
    @SuppressWarnings({"rawtypes"})
    public ExcelImportResult getReturnMap(HttpServletRequest request) {
        if ("Y".equals(this.excelTemplate.getIs_direct_import())) {
            importSuccDatas();
        }

        Map<String, Object> returnMap = new HashMap<String, Object>();
        int succs = this.modelClass != null ? this.successModels.size() : this.succMaps.size();
        int erros = this.modelClass != null ? this.errorsModels.size() : this.errorMaps.size();
        int total = erros + succs;
        ExcelImportResult result = new ExcelImportResult(total, succs, erros, this.succMaps,
                this.errorMaps, this.successModels, this.errorsModels, this.errMsg);
        SessionManager.set("errDatas", this.modelClass != null ? this.errorsModels : this.errorMaps);
        SessionManager.set("errMsg", this.errMsg);
        return result;
    }

    /**
     * 处理错误数据
     *
     * @param errMsg 错误信息列表
     * @return
     */
    @SuppressWarnings({"rawtypes"})
    public String handleerrMsg(List errMsg) {
        if (errMsg.size() == 0)
            return "";
        String errmsg = "";
        for (Object str : errMsg) {
            errmsg = errmsg + str.toString() + ",<br/>";
        }
        return errmsg;
    }

    /**
     * 下载错误数据
     *
     * @return
     */
    @SuppressWarnings({"rawtypes"})
    public void getErrorDate(HttpServletResponse response) {
        XSSFWorkbook wb;
        try {
            wb = new XSSFWorkbook();
            Sheet sheet = wb.createSheet();
            List errDatas = (List) SessionManager.get("errDatas");
            List errMsg = (List) SessionManager.get("errMsg");
            fillHeader(wb, sheet.createRow(0));
            for (int i = 0; i < errDatas.size(); i++) {
                Row row = sheet.createRow(i + 1);
                fillExcel(wb, row, errDatas.get(i), errMsg.get(i).toString(), errDatas.get(i));
            }
            File tmpFile = new File("errorData.xls");
            FileOutputStream out = new FileOutputStream(tmpFile);
            wb.write(out);
            out.close();
			   /* HttpHeaders headers = new HttpHeaders();  
		        String fileName=new String("错误信息.xlsx".getBytes("UTF-8"),"iso-8859-1");//为了解决中文名称乱码问题
		        headers.setContentDispositionFormData("attachment", fileName); 
		        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM); 
		     return     new ResponseEntity<byte[]>(FileUtils.readFileToByteArray(tmpFile),  
		                                          headers, HttpStatus.OK); */
            String fileName = "错误数据信息.xlsx";
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/x-download");
            try {
                response.addHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(), "ISO8859-1"));
            } catch (UnsupportedEncodingException e1) {
                e1.printStackTrace();
            }
            @SuppressWarnings("deprecation")
            OutputStream outp = null;
            FileInputStream in = null;
            try {
                outp = response.getOutputStream();
                in = new FileInputStream(tmpFile);
                byte[] b = new byte[1024];
                int i = 0;
                while ((i = in.read(b)) > 0) {
                    outp.write(b, 0, i);
                }
                outp.flush();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    outp.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        //return null;
    }

    /**
     * 设置excel的表格表头
     *
     * @param wb
     * @param row
     */
    public void fillHeader(XSSFWorkbook wb, Row row) {
        for (ExcelTemplateColumn dfc : list) {
            Cell cell = row.createCell(Integer.parseInt(dfc.getExcel_field_no()));
            cell.setCellValue(dfc.getCh_column_name());
        }

        Cell cell = row.createCell(list.size());
        cell.setCellValue("错误信息");
    }

    /**
     * 填充excel的错误信息
     *
     * @param wb
     * @param row
     * @param model
     * @param error
     */
    public void fillExcel(XSSFWorkbook wb, Row row, Object model, String error, Object obj) {
        try {
            if (this.modelClass != null) {

                Field[] fs = this.modelClass.getDeclaredFields();
                Method[] methods = this.modelClass.getDeclaredMethods();
                int cellNum = 0;
                for (Field f : fs) {
                    for (ExcelTemplateColumn dfc : list) {
                        if (f.getName().equals(dfc.getEn_column_name())) {
                            for (Method mt : methods) {
                                if (mt.getName().toLowerCase().startsWith("get") && mt.getName().toLowerCase().equals("get" + f.getName().toLowerCase())) {
                                    mt.setAccessible(true);
                                    Cell cell = row.createCell(Integer.parseInt(dfc.getExcel_field_no()));
                                    cell.setCellValue(mt.invoke(model, null) == null ? "" : mt.invoke(model, null).toString());
                                    cellNum++;
                                }
                            }
                        }
                    }
                }
                Cell cell = row.createCell(cellNum);
                CellStyle style = wb.createCellStyle();
                style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                style.setFillForegroundColor(IndexedColors.RED.getIndex());//设置错误信息背景颜色为红色
                cell.setCellValue(error);
                cell.setCellStyle(style);
            } else {
                int cellNum = 0;
                Map newMap = (Map) obj;
                for (ExcelTemplateColumn dfc : list) {
                    Cell cell = row.createCell(Integer.parseInt(dfc.getExcel_field_no()));
                    cell.setCellValue(newMap.get(dfc.getEn_column_name()) == null ? "" : newMap.get(dfc.getEn_column_name()).toString());
                    cellNum++;
                }
                Cell cell = row.createCell(cellNum);
                CellStyle style = wb.createCellStyle();
                style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                style.setFillForegroundColor(IndexedColors.RED.getIndex());//设置错误信息背景颜色为红色
                cell.setCellValue(error);
                cell.setCellStyle(style);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 字段的验证校验
     *
     * @param objModel
     */
    @SuppressWarnings("unchecked")
    public void validate(Integer i, Object objModel) {
        try {
            if (this.modelClass != null) {

                Method[] ms = objModel.getClass().getDeclaredMethods();
                for (ExcelTemplateColumn dfc : list) {
                    String maxLength = dfc.getMax_length();
                    String dataType = dfc.getData_type();
                    String isNull = dfc.getIs_null();
                    String format = dfc.getDate_formate();
                    String errMsg = "";
                    for (Method m : ms) {
                        if ("Y".equals(dfc.getIs_primary_key())) {
                            if (m.getName().startsWith("set") && m.getName().toLowerCase().equals("set" + dfc.getEn_column_name().toLowerCase())) {
                                m.setAccessible(true);
                                m.invoke(objModel, CommonUtil.getUUID32());
                            }
                        }
                        if (m.getName().startsWith("get") && m.getName().toLowerCase().equals("get" + dfc.getEn_column_name().toLowerCase())) {
                            m.setAccessible(true);
                            Object fieldValue = m.invoke(objModel);
                            if ("Y".equals(isNull) && (fieldValue == null || "".equals(fieldValue))) {
                                errMsg = "第" + i + "行，" + dfc.getCh_column_name() + "不允许为空";
                                this.errMsg.add(errMsg);
                                this.errorsModels.add(objModel);
                                return;
                            }
                            if (maxLength != null && "String".equals(dataType)) {
                                if (fieldValue != null) {
                                    String fieldValueStr = fieldValue.toString();
                                    int maxLen = Integer.parseInt(maxLength);
                                    if (maxLen < fieldValueStr.length()) {
                                        errMsg = "第" + i + "行，" + dfc.getCh_column_name() + "长度过长，超过" + maxLen;
                                        this.errMsg.add(errMsg);
                                        this.errorsModels.add(objModel);
                                        return;
                                    }
                                }
                            }

                            if ("Date".equals(dataType) && format != null) {
                                String fieldValueStr = fieldValue.toString();
                                boolean dateFlag = this.isValidDate(fieldValueStr, format);
                                if (!dateFlag) {
                                    errMsg = "第" + i + "行，" + dfc.getCh_column_name() + "日期格式不正确 ，日期格式应该为" + format;
                                    this.errMsg.add(errMsg);
                                    this.errorsModels.add(objModel);
                                    return;
                                }
                            }

                            if ("Y".equals(dfc.getIs_primary_key())) {
                                dfc.getIs_primary_key();
                            }
                        }
                    }
                }
                this.successModels.add(objModel);
            } else if (totalNumber - 1 == i) {
                int j = 1;
                for (Object obj : dataMaps) {
                    boolean flag = true;
                    for (ExcelTemplateColumn dfc : list) {
                        flag = true;
                        String maxLength = dfc.getMax_length();
                        String dataType = dfc.getData_type();
                        String isNull = dfc.getIs_null();
                        String format = dfc.getDate_formate();
                        String errMsg = "";
                        Object objValue = ((Map) obj).get(dfc.getEn_column_name());


                        if ("Y".equals(isNull) && (objValue == null || "".equals(objValue))) {
                            errMsg = "第" + j + "行，" + dfc.getCh_column_name() + "不允许为空";
                            this.errMsg.add(errMsg);
                            this.errorMaps.add(obj);
                            flag = false;
                            break;
                        }
                        if (maxLength != null && "String".equals(dataType)) {
                            if (objValue != null) {
                                String fieldValueStr = objValue.toString();
                                int maxLen = Integer.parseInt(maxLength);
                                if (maxLen < fieldValueStr.length()) {
                                    errMsg = "第" + j + "行，" + dfc.getCh_column_name() + "长度过长，超过" + maxLen;
                                    this.errMsg.add(errMsg);
                                    this.errorMaps.add(obj);
                                    flag = false;
                                    break;
                                }
                            }

                        }

                        if ("Date".equals(dataType) && format != null) {
                            String fieldValueStr = objValue.toString();
                            boolean dateFlag = this.isValidDate(fieldValueStr, format);
                            if (!dateFlag) {
                                errMsg = "第" + j + "行，" + dfc.getCh_column_name() + "日期格式不正确 ，日期格式应该为" + format;
                                this.errMsg.add(errMsg);
                                this.errorMaps.add(obj);
                                flag = false;
                                break;
                            }
                        }

                        if ("Y".equals(dfc.getIs_primary_key())) {
                            dfc.getIs_primary_key();
                            ((Map) obj).put(dfc.getEn_column_name(), CommonUtil.getUUID32());
                        }


                    }
                    j++;
                    if (flag)
                        this.succMaps.add(obj);

                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 验证日期excel格式
     *
     * @param str
     * @param format
     * @return
     */
    public boolean isValidDate(String str, String format) {
        DateFormat formatter = new SimpleDateFormat(format);
        try {
            Date date = (Date) formatter.parse(str);
            return str.equals(formatter.format(date));
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * 导入正确的数据
     */
    public void importSuccDatas() {
        if (this.modelClass != null) {
            for (Object obj : this.successModels) {
                this.dao.addObject(excelTemplate.getSql_id(), obj);
            }
        } else {
            for (Object obj : this.succMaps) {
                String sqlStr = "insert into " + this.excelTemplate.getTable_name() + "(";
                for (ExcelTemplateColumn dfc : list) {
                    sqlStr += dfc.getEn_column_name() + ",";
                }
                sqlStr = sqlStr.substring(0, sqlStr.length() - 1) + ")values(";
                for (ExcelTemplateColumn dfc : list) {
                    if ("String".equals(dfc.getData_type())) {
                        sqlStr += "'" + ((Map) obj).get(dfc.getEn_column_name()) + "',";
                    }
                    if ("Number".equals(dfc.getData_type()) || "Integer".equals(dfc.getData_type()) || "Double".equals(dfc.getData_type()) || "Float".equals(dfc.getData_type())) {
                        sqlStr += "" + ((Map) obj).get(dfc.getEn_column_name()) + ",";
                    }
                    if ("Date".equals(dfc.getData_type())) {
                        sqlStr += "to_date('" + ((Map) obj).get(dfc.getEn_column_name()) + "','" + dfc.getDate_formate() + "'),";
                    }
                }
                sqlStr = sqlStr.substring(0, sqlStr.length() - 1) + ") ";
                this.dao.executeSql(sqlStr);
            }
        }

    }
}
